Skip Headers

Oracle® C++ Call Interface Programmer's Guide 10g Release 2 (10.2) Part Number B14294-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 Optimizing Performance of OCCI Applications

This chapter describes a few suggestions that will lead to better performance for your OCCI custom applications.

This chapter contains these topics:

- Reading and Writing Multiple LOBs

- Transparent Application Failover

- Connection Sharing

- Application Managed Data Buffering

- Array Fetch Using next() Method

- Modifying Rows Iteratively

See Also: Chapter 12, "OCCI Application Programming Interface"

## Reading and Writing Multiple LOBs

As of Oracle Database 10g Release 2, OCCI has new interfaces that enhance application performance while reading and writing multiple `LOB`s, such as `Bfile`s, `Blob`s, `Clob`s and `NClob`s.

These interfaces have several advantages over the standard methods for reading and writing a single `LOB` at a time:

- Reading and writing multiple `LOB`s through OCCI in a single server round-trip improves performance by decreasing I/O time between the application and the back end.

- The new APIs provide support for `LOB`s that are larger than the previous limit of 4 GB. The new interfaces accept the `oraub8` datatype for amount, offsets, buffer and length parameters. These parameters are mapped to the appropriate 64-bit native datatype, which is determined by the compiler and the operating system.

- For `Clob`-related methods, the user can specify the data amount read or written in terms of character counts or byte counts.

New APIs for this features are described in Chapter 12, "OCCI Application Programming Interface", section on Connection Class, and include readVectorOfBfiles(), readVectorOfBlobs(), readVectorOfClobs() (overloaded to support general charactersets, and the `UTF16` characterset in particular), writeVectorOfBlobs(), and writeVectorOfClobs() (overloaded to support general charactersets, and the `UTF16` characterset in particular).

### Using the Interfaces for Reading and Writing Multiple LOBs

Each of the `readVectorOf` `xxx``()` and `writeVectorOf` `xxx``()` interface uses the following parameters:

- `conn`, a `Connection` class object

- vec, a vector of `LOB` objects: `Bfile`, `Blob`, or `Clob`

- `byteAmts`, array of amounts, in bytes, for reading or writing

- `charAmts`, array of amounts, in characters, for reading or writing (only applicable for `Clob`s and `NClob`s)

- `offsets`, array of offsets, in bytes for `Bfile`s and `Blob`s, in characters for `Clob`s)

- `buffers`, array of buffer pointers

- `bufferLengths`, array of buffer lengths.

If there are errors in either reading or writing of one of the `LOB`s in the vector, the whole operation is cancelled. The `byteAmts` or `charAmts` parameters should be checked to determine the actual number of bytes or characters read or written.

## Transparent Application Failover

OCCI Transparent Application Failover enables OCCI to be more robust in handling database instance failures in distributed applications at run time. If a server node becomes unavailable, applications will automatically reconnect to another surviving node.

Some design options should be considered when including Transparent Application Failover in an application:

- Because of the delays inherent to failover processing, the design of the application may include a notice to the user that a failover is in progress and that normal operation should resume shortly.

- If the session on the initial instance received `ALTER SESSION` commands before the failover began, they will not be automatically replayed on the second instance.

  Consequently, the developer may wish to replay these `ALTER SESSION` commands on the second instance.

Note: It is the user's responsibility to track changes to the `SESSION` parameters.

To address these problems, the application can register a failover callback function. In the event of failover, the callback function is invoked at different times during the course of reestablishing the user's session.

- The first call to the callback function occurs when Oracle first detects an instance connection loss. This callback is intended to allow the application to inform the user of an upcoming delay.

- If failover is successful, a second call to the callback function occurs when the connection is reestablished and usable. At this time the client may wish to replay `ALTER SESSION` commands and inform the user that failover has happened. Note that you must keep track of `SESSION` parameter changes and then replay them after the failover is complete.

  If failover is unsuccessful, then the callback function is called to inform the application that failover will not take place.

- An initial attempt at failover may not always successful. The failover callback should return `FO_RETRY` to indicate that the failover should be attempted again.

  See Also: Definition of `FailOverType` and `FailOverEventType` in Table 12-11, "Enumerated Values Used by Connection Class" in Chapter 12, "OCCI Application Programming Interface" Oracle Net Services Reference Guide for more detailed information about application failover.

### Using Transparent Application Failover

To enable TAF, the connect string has to be configured for failover and registered on `Connection` (created from `Environment`, `ConnectionPool` and `StatelessConnectionPool`). To register the callback function, use the Connection Class interface setTAFNotify().

    void Connection::setTAFNotify(
       int (*notifyFn)(
          Environment *env,
          Connection *conn,
          void *ctx,
          FailOverType foType,
          FailOverEventType foEvent),
       void *ctxTAF);

Note that TAF support for `ConnectionPool`s does not include `BACKUP` and `PRECONNECT` clauses; these should not be used in the connect string.

### Objects and Transparent Application Failover

Transparent application failover works with the OCCI navigational and associative access models and the object cache. In a non-RAC setup, you must ensure that the object type definitions and object OIDs in primary and backup instances are identical.

If the application receives `ORA-25402: transaction must roll back` error after the failover, then it must initiate a rollback to correctly reset the object cache on the client. If a transaction has not started before the failover, the application should still initiate a rollback after the failover to refresh the objects on the client object cache from the new instance.

### Connection Pooling and Transparent Application Failover

If the transparent application failover feature is activated, connections created in a connection pool are also failed over. The application failover callback needs to be specified for each connection obtained from the connection pool; these connections will be failed over when used after the primary instance failure.

## Connection Sharing

This section covers the following topics:

- Introduction to Thread Safety

- Implementing Thread Safety

- Serialization

### Introduction to Thread Safe ty

Threads are lightweight processes that exist within a larger process. Threads each share the same code and data segments, but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads. The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), which ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In OCCI, mutexes are granted on an OCCI environment basis.

This thread safety feature of the Oracle database server and OCCI library enables developers to use OCCI in a multithreaded application with these added benefits:

- Multiple threads of execution can make OCCI calls with the same result as successive calls made by a single thread.

- When multiple threads make OCCI calls, there are no side effects between threads.

- Even if you do not write a multithreaded program, you do not pay any performance penalty for including thread-safe OCCI calls.

- Use of multiple threads can improve program performance. You can discern gains on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

In addition to client/server applications, where the client can be a multithreaded program, thread safety is typically used in three-tier or client/agent/server architectures. In this architecture, the client is concerned only with presentation services. The agent (or application server) processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in the three-tier architecture is an Oracle database server. The applications server (agent) supports multithreading, with each thread serving a separate client application. In an Oracle environment, this middle-tier application server is an OCCI or precompiler program.

### Implementing Threa d Safety

In order to take advantage of thread safety by using OCCI, an application must be running in a thread-safe operating system. Then the application must inform OCCI that the application is running in multithreaded mode by specifying `THREADED_MUTEXED` or `THREADED_UNMUTEXED` for the mode parameter of the `createEnvironment()` method. For example, to turn on mutual exclusivity locking, issue the following statement:

    Environment *env = Environment::createEnvironment(
              Environment::THREADED_MUTEXED);

Note that once `createEnvironment` is called with `THREADED_MUTEXED` or `THREADED_UNMUTEXED`, all subsequent calls to the `createEnvironment` method must also be made with `THREADED_MUTEXED` or `THREADED_UNMUTEXED` modes.

If a multithreaded application is running in a thread-safe operating system, then the OCCI library will manage mutexes for the application on a for each-OCCI-environment basis. However, you can override this feature and have your application maintain its own mutex scheme. This is done by specifying a mode value of `THREADED_UNMUTEXED` to the `createEnvironment()` method.

Note: Applications running on non-thread-safe platforms should not pass a value of `THREADED_MUTEXED` or `THREADED_UNMUTEXED` to the `createEnvironment()` method. If an application is single threaded, whether or not the platform is thread safe, the application should pass a value of `Environment::DEFAULT` to the `createEnvironment` method. This is also the default value for the mode parameter. Single threaded applications which run in `THREADED_MUTEXED` mode may incur performance degradation.

### Serialization

As an application programmer, you have two basic options regarding concurrency in a multithreaded application:

- Automatic serialization, in which you utilize OTIS's transparent mechanisms

- Application-provided serialization, in which you manage the contingencies involved in maintaining multiple threads

#### Automatic Serialization

In cases where there are multiple threads operating on objects (connections and connection pools) derived from an OCCI environment, you can elect to let OCCI serialize access to those objects. The first step is to pass a value of `THREADED_MUTEXED` to the `createEnvironment` method. At this point, the OCCI library automatically acquires a mutex on thread-safe objects in the environment.

When the OCCI environment is created with `THREADED_MUTEXED` mode, then only the `Environment`, `Map`, `ConnectionPool`, `StatelessConnectionPool` and `Connection` objects are thread-safe. That is, if two threads make simultaneous calls on one of these objects, then OCCI serializes them internally. However, note that all other OCCI objects, such as `Statement`, `ResultSet`, `SQLException`, `Stream`, and so on, are not thread-safe as, applications should not operate on these objects simultaneously from multiple threads.

Note that the bulk of processing for an OCCI call happens on the server, so if two threads that use OCCI calls go to the same connection, then one of them could be blocked while the other finishes processing at the server.

#### Application-Provided Serialization

In cases where there are multiple threads operating on objects derived from an OCCI environment, you can chose to manage serialization. The first step is to pass a value of `THREADED_UNMUTEXED` for the `createEnvironment` mode. In this case the application must mutual exclusively lock OCCI calls made on objects derived from the same OCCI environment. This has the advantage that the mutex scheme can be optimized based on the application design to gain greater concurrency.

When an OCCI environment is created in this mode, OCCI recognizes that the application is running in a multithreaded application, but that OCCI need not acquire its internal mutexes. OCCI assumes that all calls to methods of objects derived from that OCCI environment are serialized by the application. You can achieve this two different ways:

- Each thread has its own environment. That is, the environment and all objects derived from it (connections, connection pools, statements, result sets, and so on) are not shared across threads. In this case your application need not apply any mutexes.

- If the application shares an OCCI environment or any object derived from the environment across threads, then it must serialize access to those objects (by using a mutex, and so on) such that only one thread is calling an OCCI method on any of those objects.

Basically, in both cases, no mutexes are acquired by OCCI. You must ensure that only one OCCI call is in process on any object derived from the OCCI environment at any given time when `THREADED_UNMUTEXED` is used.

Note: OCCI is optimized to reuse objects as much as possible. Since each environment has its own heap, multiple environments result in increased consumption of memory. Having multiple environments may imply duplicating work with regard to connections, connection pools, statements, and result set objects. This will result in further memory consumption. Having multiple connections to the server results in more resource consumptions on the server and network. Having multiple environments would normally entail more connections.

## Application Managed Data Buffering

When you provide data for bind parameters by the `set` xxx methods in parameterized statements, the values are copied into an internal data buffer, and the copied values are then provided to the database server for insertion. To reduce overhead of copying `string` type data that is available in user buffers, use the setDataBuffer() and next() methods of the ResultSet Class and the execute() method of the Statement Class.

### setD ataBuffer() Method

For high performance applications, OCCI provides the `setDataBuffer` method whereby the data buffer is managed by the application. The following example shows the setDataBuffer() method:

    void setDataBuffer(int paramIndex,
       void *buffer,
       Type type,
       sb4 size,
       ub2 *length,
       sb2 *ind = NULL,
       ub2 *rc = NULL);

The following parameters are used in the previous method example:

- `paramIndex`: Parameter number

- `buffer`: Data buffer containing data

- `type`: Type of the data in the data buffer

- `size`: Size of the data buffer

- `length`: Current length of data in the data buffer

- `ind`: Indicator information. This indicates whether the data is `NULL` or not. For parameterized statements, a value of `-1` means a `NULL` value is to be inserted. For data returned from callable statements, a value of `-1` means `NULL` data is retrieved.

- `rc`: Return code. This variable is not applicable to data provided to the `Statement` method. However, for data returned from callable statements, the return code specifies parameter-specific error numbers.

Not all datatypes can be provided and retrieved by means of the `setDataBuffer()` method. For instance, C++ Standard Library strings cannot be provided with the `setDataBuffer()` interface.

See Also: Table 5-2, "External Datatypes and Corresponding C++ and OCCI Types" in Chapter 5, "Datatypes" for specific cases

There is an important difference between the data provided by the `set` xxx`()` methods and `setDataBuffer()` method. When data is copied in the `set` xxx`()` methods, the original can change once the data is copied. For example, you can use a `setString(str1)` method, then change the value of `str1` prior to execute. The value of `str1` that is used is the value at the time `setString(str1)` is called. However, for data provided by means of the `setDataBuffer()` method, the buffer must remain valid until the execution is completed.

If iterative executes or the `executeArrayUpdate()` method is used, then data for multiple rows and iterations can be provided in a single buffer. In this case, the data for the ith iteration is at `buffer + (i-1) *size address` and the length, indicator, and return codes are at `*(length + i)`, `*(ind + i)`, and `*(rc + i)` respectively.

This interface is also meant for use with array executions and callable statements that have array or `OUT` bind parameters.

The same method is available in the `ResultSet` class to retrieve data without re-allocating the buffer for each fetch.

### executeArra yUpdate() Method

If all data is provided with the `setDataBuffer()` methods or output streams (that is, no `set` xxx() methods besides `setDataBuffer()` or `getStream()` are called), then there is a simplified way of doing iterative execution.

In this case, you should not call `setMaxIterations()` and `setMaxParamSize()`. Instead, call the `setDataBuffer()` or `getStream()` method for each parameter with the appropriate size arrays to provide data for each iteration, followed by the `executeArrayUpdate(int` `arrayLength)` method. The `arrayLength` parameter specifies the number of elements provided in each buffer. Essentially, this is same as setting the number of iterations to `arrayLength` and executing the statement.

Since the stream parameters are specified only once, they can be used with array executes as well. However, if any `set` xxx`()` methods are used, then the `addIteration()` method is called to provide data for multiple rows. To compare the two approaches, consider an example that inserts two employees in the `emp` table:

    Statement *stmt = conn->createStatement("insert into emp (id, ename)
       values(:1, :2)");
    char enames[2][] = {"SMITH", "MARTIN"};
    ub2 enameLen[2];
    for (int i = 0; i < 2; i++)
       enameLen[i] = strlen(enames[i] + 1);
    stmt->setMaxIteration(2);             // set maximum number of iterations
    stmt->setInt(1, 7369);                // specify data for the first row
    stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &enameLen);
    stmt->addIteration();
    stmt->setInt(1, 7654);                // specify data for the second row

    // a setDatBuffer is unnecessary for the second bind parameter as data
    // provided through setDataBuffer is specified only once.
    stmt->executeUpdate();

However, if the first parameter could also be provided through the `setDataBuffer()` interface, then, instead of the `addIteration()` method, you would use the `executeArrayUpdate()` method:

    stmt ->setSQL("insert into emp (id, ename) values (:1, :2)");
    char enames[2][] = {"SMITH", "MARTIN"};
    ub2 enameLen[2];
    for (int i = 0; i < 2; i++)
       enameLen[i] = strlen(enames[i] + 1);
    int ids[2] = {7369, 7654};
    ub2 idLen[2] = {sizeof(ids[0], sizeof(ids[1])};
    stmt->setDataBuffer(1, ids, OCCIINT, sizeof(ids[0]), &idLen);
    stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &len);
    stmt->executeArrayUpdate(2);          // data for two rows is inserted.

## Array Fetch Using next() Method

If the application is fetching data with only the `setDataBuffer()` interface or the stream interface, then an array fetch can be executed. The array fetch is implemented through the `next(`) method of the `ResultSet` class. You must process the results obtained through next() before calling it again.

Example 11-1 How to use Array Fetch with a ResultSet

    ResultSet *resultSet = stmt->executeQuery(...);
    resultSet->setDataBuffer(...);
    while (resultSet->next(numRows) == DATA_AVAILABLE)
       process(resultSet->getNumArrayRows() );

This causes up to `numRows` amount of data to be fetched for each column. The buffers specified with the `setDataBuffer()` interface should large enough to hold at least `numRows` of data.

## Modifying Rows Iteratively

To process batch errors, specify that the `Statement` object is in a `batchMode` of execution using the setBatchErrorMode() method. Once the `batchMode` is set and a batch update runs, any resulting errors are reported through the BatchSQLException Class.

The `BatchSQLException` class provides methods that handle batch errors. Example 11-2 illustrates how batch handling can be implemented within any OCCI application.

Example 11-2 How to Modify Rows Iteratively and Handle Errors

1. Create the Statement object and set its batch error mode to `TRUE`.

       Statement *stmt = conn->createStatement ("...");
       stmt->setBatchErrorMode (true);

2. Perform programmatic changes necessary by the application.

3. Update the statement.

       try {
       updateCount = stmt->executeUpdate ();
       }

4. Catch and handle any errors generated during the batch insert or update.

       catch (BatchSQLException &batchEx)
       {
        cout<<"Batch Exception: "<<batchEx.what()<<endl;
        int errCount = batchEx.getFailedRowCount();
        cout << "Number of rows failed " << errCount <endl;
        for (int i = 0; i < errCount; i++ )
        {
          SQLException err = batchEx.getException(i);
          unsigned int rowIndex = batchEx.getRowNum(i);
          cout<<"Row " << rowIndex << " failed because of "
               << err.getErrorCode() << endl;
        }
          // take recovery action on the failed rows
       }

5. Catch and handle other errors generated during the statement update. Note that statement-level errors are still thrown as instances of a `SQLException`.

       catch( SQLException &ex) // to catch other SQLExceptions.
       {
          cout << "SQLException: " << e.what() << endl;
       }